Populate the Staging tables

*Create a new SSIS project.

  1. Open Microsoft Visual Studio ==> Right click on File ==> Select New ==> Click on Project.
  2. The New Project dialog box will appear, Then Click on Business Intelligence ==> Right on click Integration Service ==> Select Integration Service Project ==> Name a new project ==> Click Ok.



*Download the C# Script attached at the beginning  

  1. Create a new SSIS package inside a newly created SSIS project.
  2. Darg and drop SSIS Data Flow Task inside control flow ==> Double click on Data Flow Task to go inside.
  3. Darg and drop SSIS Script Component inside Data Flow Task from SSIS toolbox ==> Double click Script Componet ==> Click on Inputs and Outputs ==> Click on plus(+) next output ==> Click on plus(+) next to output Columns ==> Set up all neccessary Output Columns.
  4. Click on Script ==>  Click on Edit Script this will opens up Microsoft Visual Studio ==> Copy  C# Script code that had been attached at the beginning ==> past it here inside Microsoft Visual Studio.
  5. Click on Save ==> CloseMicrosoft Visual Studio ==> Click ok on Script Transformation Editor dialog box.




TRUNCATE TABLE  US_Mass_shooting_1966_2017_Stg





*All necessary data clean-ups, data validation, and data conversion need to be done before data loaded from source JSON files to the destination SQL Server staging database. 

  1. Drag and drop the Data Conversion transformation ==> to convert the appropriate Data Types.
  2. Drag and drop Derived Column Transformation ==> Copy and paste the following code inside the Derived Column transformation Editor Expression field. Class == "MS" ? "Mass Shooting" : Class == "FMS" ? "Family Murder Suicide" : Class == "SPK" ? "Spree Killing" : Class == "SEK" ? "Serial Killing" : Class == "GD" ? "Gang or Drug Related" : Class


  1. Drag and drop the OLEDB Destination ==> Configure the destination connection manager ==> select the staging table called "US_Mass_Shooting_1966_2017_Stg" from the Name of the table or the view drop-down list.
  2. Mapp source columns to destination columns.
  3. Click on Start to run the package.



*To make sure all necessary data loaded from source to destination, Retrieve all records from Destination SQL server staging database tables.

*Copy the following SQL Script and past it inside Microsoft SQL Server Management Studio ==> Click Execute. This will retrieve records from two staging tables.


USE [CAP_Project_One_Staging]

GO


SELECT * FROM [dbo].[US_Mass_shooting_1966_2017_Stg]


SELECT * FROM [dbo].[US_Mass_Shooting_1966_2019]







*After all dictionary tables scheme has been created, then go back to the files that had been downloaded at the beginning ==> locate the fellowing two SQL query files ==> Double click on it(open it in SSMS) ==> Click execute. This will create two view tables inside the staging database that will be used to populate dictionary tables.

"US_Mass_Shooting_1966_2019_view "
" US_Mass_Shooting_Stg_View"




https://www.sentryone.com/create-ssis-package-guide